--EXEC SP_CREATE_UPDATE_PARTAIL_BIN_INFO '1830C13-U05','6KAF47183.1',8000, 24, 'Die Bank', 'SPEL', '6KAF47183.1' 
CREATE PROCEDURE SP_CREATE_UPDATE_PARTAIL_BIN_INFO
(
  @PART_NUMBER VARCHAR(50),
  @LOT_NUMBER  VARCHAR(50),
  @BIN_QTY	   INT,
  @WAFER_ID	   INT,
  @PS_TYPE_NAME VARCHAR(50),
  @VENDOR_NAME VARCHAr(50),
  @FAB_LOT_NUMBER VARCHAR(50)		 	
)
AS
BEGIN
		
	DECLARE @LOT_ID INT
 	DECLARE @PROCESS_STEP_ID INT
 	DECLARE @LOCATION_ID INT
	
 	SELECT @LOCATION_ID = LOCATION_ID FROM LOCATION WHERE L_TYPE = @PS_TYPE_NAME AND L_VENDOR_NAME = @VENDOR_NAME
	SELECT @PROCESS_STEP_ID = PROCESS_STEP_ID FROM FN_GET_PROCESS_STEP_ID(@PART_NUMBER,@PS_TYPE_NAME,@PS_TYPE_NAME )
	
	IF @PROCESS_STEP_ID IS NULL 
	BEGIN
		 -- DUMMY UPDATE TO AVOID SQLEXCEPTION
		IF ( SELECT COUNT(*) FROM NOTIFICATION_ALERT_TABLE WHERE PART_NUMBER = @PART_NUMBER AND LOT_NUMBER = @LOT_NUMBER AND FAB_LOT_NUMBER = @lot_number AND VENDOR_NAME = @VENDOR_NAME AND PROCESS_TYPE = @PS_TYPE_NAME AND IS_USER_NOTIFIED = 1 ) = 0 
		BEGIN
			INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Die_Qty, Wafer_id, Status, TimeStamp )
				VALUES ( @part_number, @lot_number, @fab_lot_number, @vendor_name, @PS_TYPE_NAME,'Discrepancy Lot Notification', @BIN_QTY, @wafer_Id, 'Open', CURRENT_TIMESTAMP )
		END
		UPDATE CLIENT_TABLE SET [NAME] = 'LOT_NUMBER' WHERE [NAME] = 'LOT_NUMBER'
		RETURN
	END

	SELECT @LOT_ID = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESS_STEP_ID AND LOCATION_ID = @LOCATION_ID AND LOT_NUMBER = @LOT_NUMBER
 	
	IF @LOT_ID IS NULL
	BEGIN
		INSERT INTO LOT_TABLE (PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, QTY_IN, DATE_IN, LOT_CURRENT_STATUS, TIMESTAMP,LOT_ENTERED_BY, LOT_CATEGORY, LOT_DROP_OUT) 
			VALUES (@PROCESS_STEP_ID, @LOCATION_ID, @LOT_NUMBER,@FAB_LOT_NUMBER, @LOT_NUMBER, @BIN_QTY, CURRENT_TIMESTAMP, 'Running', CURRENT_TIMESTAMP, 1, 'PROD', 0 )
		SELECT @LOT_ID = @@IDENTITY
	END
	
	IF @LOT_ID IS NOT NULL AND @LOT_ID > 0  		
 	BEGIN
		DECLARE @LOT_STATUS VARCHAR(50)
		
		SELECT @LOT_STATUS = LOT_CURRENT_STATUS FROM LOT_TABLE WHERE LOT_ID = @LOT_ID
		
		IF @LOT_STATUS = 'Close'
		BEGIN
				UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = 'Running' , QTY_IN = @BIN_QTY  WHERE LOT_ID = @LOT_ID
		END
		ELSE
		BEGIN
		       UPDATE LOT_TABLE SET QTY_IN = QTY_IN + @BIN_QTY  WHERE LOT_ID = @LOT_ID
		END
		IF ( SELECT COUNT(*) FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID )  = 0
		BEGIN
			INSERT INTO SORT_BIN_RELEASE_TABLE ( LOT_ID, WAFER_ID, BIN_1, BIN_1_BALANCE_QTY ) 
				VALUES ( @LOT_ID, @WAFER_ID,@BIN_QTY, @BIN_QTY )
		END
		ELSE
		BEGIN
			UPDATE SORT_BIN_RELEASE_TABLE SET BIN_1_BALANCE_QTY =  @BIN_QTY WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID
		END
	END--END OF ELSE 
END